﻿USE [BizTalkDTADb]
GO
/****** Object:  UserDefinedFunction [dbo].[Onatica_GetRootServiceInstanceId]    Script Date: 02/05/2015 16:35:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Onatica_GetRootServiceInstanceId]
(
	@ServiceInstanceId uniqueidentifier
)
RETURNS uniqueidentifier
AS
BEGIN
	DECLARE @RootServiceInstanceId AS uniqueidentifier;
	DECLARE @Links TABLE (
		[FowTemplate] [varchar](3) NULL,
		[FromEventId] [bigint] NULL,
		[FromMessageInstanceId] [uniqueidentifier] NULL,
		[FromServiceInstanceId] [uniqueidentifier] NULL,
		[FromStatus] [int] NULL,
		[FromComponentName] [nvarchar](256) NULL,
		[FromServiceName] [nvarchar](256) NULL,
		[FromServiceType] [nvarchar](256) NULL,
		[FromActivityId] [uniqueidentifier] NULL,
		[FromTimestamp] [datetime] NULL,
		[FromIs2Way] [int] NULL,
		[ToEventId] [bigint] NULL,
		[ToMessageInstanceId] [uniqueidentifier] NULL,
		[ToServiceInstanceId] [uniqueidentifier] NULL,
		[ToStatus] [int] NULL,
		[ToComponentName] [nvarchar](256) NULL,
		[ToServiceName] [nvarchar](256) NULL,
		[ToServiceType] [nvarchar](256) NULL,
		[ToActivityId] [uniqueidentifier] NULL,
		[ToTimestamp] [datetime] NULL,
		[MessageServiceInstance] [uniqueidentifier] NULL,
		[Generation] [int] NULL
	);
	
	--Recursive call
	WITH cteLinkedEvents AS
	(
		SELECT
			 mioe.nEventId
			,mioe.nPortId
			,mioe.uidServiceInstanceId
			,mioe.uidActivityId
			,mioe.uidMessageInstanceId
			,mioe.nStatus
			,mioe.dtTimestamp
			,srvI.uidServiceId
			,srvI.ErrorInfo
			,port.strPortName
			,srv.strServiceType
			,srv.strServiceName
		From
			dta_MessageInOutEvents mioe WITH (NOLOCK)
			INNER JOIN dta_ServiceInstances srvI WITH (NOLOCK)
				ON mioe.uidServiceInstanceId = srvI.uidServiceInstanceId
				AND mioe.uidActivityId = srvI.uidActivityId
			INNER JOIN dta_PortName port WITH (NOLOCK)
				ON mioe.nPortId = port.nPortId
			INNER JOIN dta_Services srv WITH (NOLOCK)
				ON srvI.uidServiceId = srv.uidServiceId
	)
	,cteLinks AS
	(
	Select 
		'N/A' FowTemplate,
		FromEvt.nEventId FromEventId,
		FromEvt.uidMessageInstanceId FromMessageInstanceId,
		FromEvt.uidServiceInstanceId FromServiceInstanceId,
		FromEvt.nStatus FromStatus,
		CASE 
			WHEN FromEvt.strServiceType = 'Orchestration' THEN FromEvt.strServiceName 
			ELSE FromEvt.strPortName
		END FromComponentName,
		FromEvt.strServiceName FromServiceName,
		FromEvt.strServiceType FromServiceType,
		FromEvt.uidActivityId FromActivityId,
		FromEvt.dtTimestamp FromTimestamp,
		2 FromIs2Way,
		ToEvtSendPort.nEventId ToEventId,
		CASE 
			WHEN ToEvt.strServiceType = 'Orchestration' AND ToEvtSendPort.strServiceType = 'Pipeline' THEN ToEvt.uidMessageInstanceId
			WHEN ToEvt.strServiceType = 'Orchestration' AND ToEvtSendPort.strServiceType = 'Orchestration'  THEN ToEvtSendPort.uidMessageInstanceId
			ELSE ToEvt.uidMessageInstanceId
		END ToMessageInstanceId,
		ToEvtSendPort.uidServiceInstanceId ToServiceInstanceId,
		ToEvt.nStatus ToStatus,
		CASE 
			WHEN ToEvtSendPort.strServiceType = 'Orchestration' THEN ToEvtSendPort.strServiceName 
			ELSE ToEvtSendPort.strPortName
		END  ToComponentName,
		ToEvtSendPort.strServiceName ToServiceName,
		ToEvtSendPort.strServiceType ToServiceType,
		ToEvtSendPort.uidActivityId ToActivityId,
		ToEvtSendPort.dtTimestamp ToTimestamp,
		ToEvtSendPort.uidServiceInstanceId MessageServiceInstance,
		0 Generation
	From 
			cteLinkedEvents FromEvt
		INNER JOIN cteLinkedEvents ToEvt
			ON 
			FromEvt.uidMessageInstanceId <> ToEvt.uidMessageInstanceId
			--AND FromEvt.uidServiceInstanceId <> ToEvt.uidServiceInstanceId
			AND FromEvt.uidServiceInstanceId = ToEvt.uidServiceInstanceId
			AND ToEvt.nStatus = 0 --1
		INNER JOIN cteLinkedEvents ToEvtSendPort
			ON ToEvt.uidMessageInstanceId = ToEvtSendPort.uidMessageInstanceId
			AND ToEvt.uidServiceInstanceId <> ToEvtSendPort.uidServiceInstanceId
			AND ToEvt.nStatus = 0 --1 
			AND ToEvtSendPort.nStatus = 1 --0
	Where FromEvt.uidServiceInstanceId = @ServiceInstanceId
		 
UNION ALL
	Select
		FromEvt.FowTemplate FowTemplate,
		FromEvt.ToEventId FromEventId,
		FromEvt.ToMessageInstanceId FromMessageInstanceId,
		FromEvt.ToServiceInstanceId FromServiceInstanceId,
		FromEvt.ToStatus FromStatus,
		FromEvt.ToComponentName FromComponentName,
		FromEvt.ToServiceName FromServiceName,
		FromEvt.ToServiceType FromServiceType,
		FromEvt.ToActivityId FromActivityId,
		FromEvt.ToTimestamp FromTimestamp,
		0 FromIs2Way,
		ToEvtSendPort.nEventId ToEventId,
		CASE 
			WHEN ToEvt.strServiceType = 'Orchestration' 
				AND ToEvtSendPort.strServiceType = 'Pipeline' THEN ToEvt.uidMessageInstanceId
			WHEN ToEvt.strServiceType = 'Orchestration' 
				AND ToEvtSendPort.strServiceType = 'Orchestration'  THEN ToEvtSendPort.uidMessageInstanceId
			ELSE ToEvt.uidMessageInstanceId
		END ToMessageInstanceId,
		CASE 
			WHEN ToEvt.strServiceType = 'Orchestration' AND ToEvtSendPort.strServiceType = 'Pipeline' THEN 
			ToEvt.uidServiceInstanceId
			WHEN ToEvt.strServiceType = 'Orchestration' AND ToEvtSendPort.strServiceType = 'Orchestration'  THEN 
			ToEvtSendPort.uidServiceInstanceId
			ELSE ToEvt.uidServiceInstanceId
		END 
		ToServiceInstanceId,
		ToEvt.nStatus ToStatus,
		CASE 
			WHEN ToEvtSendPort.strServiceType = 'Orchestration' THEN ToEvtSendPort.strServiceName 
			ELSE ToEvtSendPort.strPortName
		END  ToComponentName,
		ToEvtSendPort.strServiceName ToServiceName,
		ToEvtSendPort.strServiceType ToServiceType,
		ToEvtSendPort.uidActivityId ToActivityId,
		ToEvtSendPort.dtTimestamp ToTimestamp,
		ToEvtSendPort.uidServiceInstanceId MessageServiceInstance,
		FromEvt.Generation - 1 Generation
	From 
			cteLinks FromEvt
		INNER JOIN cteLinkedEvents ToEvt
			ON FromEvt.ToServiceInstanceId = ToEvt.uidServiceInstanceId
			AND FromEvt.ToMessageInstanceId <> ToEvt.uidMessageInstanceId
			AND FromEvt.FromServiceInstanceId <> ToEvt.uidServiceInstanceId
			AND ToEvt.nStatus = 0--1
		INNER JOIN cteLinkedEvents ToEvtSendPort
			ON ToEvt.uidMessageInstanceId = ToEvtSendPort.uidMessageInstanceId
			AND ToEvt.uidServiceInstanceId <> ToEvtSendPort.uidServiceInstanceId
			AND ToEvt.nStatus = 0--1 
			AND ToEvtSendPort.nStatus = 1--0
	Where FromEvt.FromServiceInstanceId  <>  ToEvtSendPort.uidServiceInstanceId 
	
--2WaySendPort link to previous service
UNION ALL
	Select
		FromEvt.FowTemplate FowTemplate,
		fromEvt.ToEventId FromEventId,
		FromEvt.ToMessageInstanceId FromMessageInstanceId,
		FromEvt.ToServiceInstanceId FromServiceInstanceId,
		FromEvt.ToStatus FromStatus,
		FromEvt.ToComponentName FromComponentName,
		FromEvt.ToServiceName FromServiceName,
		FromEvt.ToServiceType FromServiceType,
		FromEvt.ToActivityId FromActivityId,
		FromEvt.ToTimestamp FromTimestamp,
		1 FromIs2Way,
		ToEvtSendPort2.nEventId ToEventId,
		ToEvtSendPort2.uidMessageInstanceId ToMessageInstanceId,
		FromEvt.FromMessageInstanceId ToServiceInstanceId,
		ToEvtSendPort2.nStatus ToStatus,
		CASE 
			WHEN ToEvtSendPort2.strServiceType = 'Orchestration' THEN ToEvtSendPort2.strServiceName 
			ELSE ToEvtSendPort2.strPortName
		END  ToComponentName,
		ToEvtSendPort2.strServiceName ToServiceName,
		ToEvtSendPort2.strServiceType ToServiceType,
		ToEvtSendPort2.uidActivityId ToActivityId,
		ToEvtSendPort2.dtTimestamp ToTimestamp,
		ToEvtSendPort2.uidServiceInstanceId  MessageServiceInstance,
		FromEvt.Generation - 1 Generation
	From 
		cteLinks FromEvt
		INNER JOIN cteLinkedEvents ToEvtSendPort
			ON FromEvt.MessageServiceInstance = ToEvtSendPort.uidServiceInstanceId
			AND FromEvt.ToMessageInstanceId <> ToEvtSendPort.uidMessageInstanceId 
			AND FromEvt.ToActivityId <> ToEvtSendPort.uidActivityId
			AND FromEvt.ToStatus = 0--1
			AND ToEvtSendPort.nStatus = 1--0
			AND FromEvt.ToTimestamp >= ToEvtSendPort.dtTimestamp -- ensure we only link those that follow
		INNER JOIN cteLinkedEvents ToEvt
			ON FromEvt.FromServiceInstanceId = ToEvt.uidServiceInstanceId
			AND	FromEvt.ToMessageInstanceId <> ToEvt.uidMessageInstanceId
			AND ToEvt.nStatus = 1--0
		INNER JOIN cteLinkedEvents ToEvtSendPort2
			ON ToEvt.uidMessageInstanceId = ToEvtSendPort2.uidMessageInstanceId
			AND FromEvt.FromServiceInstanceId = ToEvtSendPort2.uidServiceInstanceId
			AND FromEvt.FromMessageInstanceId = ToEvtSendPort2.uidMessageInstanceId
)

	Insert into @Links
	Select *
	From cteLinks link

	SELECT TOP 1 @RootServiceInstanceId = ISNULL(l.MessageServiceInstance,@ServiceInstanceId)
	FROM @Links l
	WHERE l.ToServiceType = 'Pipeline'
	ORDER BY ToTimestamp;

	RETURN @RootServiceInstanceId;
END
